from web import database, sqlquote
import logging

# Setup logging

logger = logging.getLogger('root.webdb')
logger.addHandler(logging.NullHandler())

# Initialize

db = None

db = database(dbn="sqlite",db="web.db")

#
# Device class that helps retrieving information from database regarding
# specific device
#

class Device:
    
    def __init__(self,storage):

        # members
        self.id = None
        self.name = None
        self.desc = None
        self.location = None
        self.role = None
        self.addresses = {}
        self.created = None

        if type(storage) is int:
            self.id = storage
            return
                
        self.id = storage['id']
        self.name = storage['name']
        self.desc = storage['desc']
        self.created = storage['created']

        if storage.has_key('location'):
            self.location = storage['location']

        if storage.has_key('role'):
            self.role = storage['role']

    def update(self):
        try:
            results = db.query( \
                "SELECT `name`,`desc`,`created` FROM DEVICES" \
                " WHERE DEVICES.id = " + sqlquote(self.id) )
            row = results[0]
            self.name = row.name
            self.desc = row.desc
            self.created = row.created
        except:
            logging.exception("updating device name and desc")
            pass
        
    def update_location(self):
        try:
            results = db.query(
                "SELECT LOCATIONS.name FROM LOCATIONS, DEVICES"
                " WHERE DEVICES.location = LOCATIONS.id AND DEVICES.id = "+ sqlquote(self.id) )
            self.location = results[0].name
        except:
            logger.exception("updating device location")
            pass
        
    def update_role(self):
        try:
            results = db.query(
                "SELECT ROLES.name FROM ROLES, DEVICES"
                " WHERE DEVICES.role = ROLES.id AND DEVICES.id = " + sqlquote(self.id) )
            self.role = results[0].name
        except:
            logger.exception("updating device role")
            pass
        
    def update_addresses(self):
        try:
            results = db.query(
                "SELECT address, endpoint FROM ENDPOINT_ADDRS"
                " WHERE device = " + sqlquote(self.id) )

            for x in results:
                ret = self.addresses.get(str(x.endpoint),None)
                if ret == None:
                    self.addresses[str(x.endpoint)] = [x.address]
                else:
                    self.addresses[str(x.endpoint)].append(x.address)
        except:
            logger.exception("updating device endpoint addresses")
            pass

    def update_all(self):
        self.get_name()
        self.get_role()
        self.get_location()
        self.get_addresses()
        
    def get_name(self):
        if self.name is not None:
            return self.name
        self.update()
        return self.name

    def get_desc(self):
        if self.desc is not None:
            return self.desc
        self.update()
        return self.desc

    def get_create_date(self):
        if self.created is not None:
            return self.created
        self.update()
        return self.created

    def get_location(self):
        if self.location is not None:
            return self.location

        self.update_location()
        return self.location

    def get_role(self):
        if self.role is not None:
            return self.role
        
        self.update_role()
        return self.role

    def get_addresses(self):
        if len(self.addresses) != 0:
            return self.addresses

        self.update_addresses()
        return self.addresses

    def get_endpoints(self):

        # get addresses map first
        addrs = self.get_addresses()
        endpoints = []

        # for each endpoint
        for eddr_entry in addrs.iteritems():

            # create endpoint and populate with addreses
            ep = Endpoint(int(eddr_entry[0]))
            ep.addrs = eddr_entry[1]
            endpoints.append(ep)

        return endpoints

    def query_capabilities(self):
        results = db.query("SELECT * FROM [VIEW_DEVICES_CAPS] WHERE device = " + sqlquote(self.id))
        return results

    def query_requests(self):
        results = db.query("SELECT * FROM [ViEW_DEVICE_REQUESTS] WHERE device = " + sqlquote(self.id))
        return results

    def query_history_caps(self,conditions=[],select="*"):

        condition_query = ""
        for condition in conditions:
            condition_query += " AND " + condition

        mainquery = "SELECT %s FROM [VIEW_HISTORY_CAPS] WHERE device = %s%s" \
                    " ORDER BY date(time,'unixepcch') DESC" % (select, sqlquote(self.id), condition_query)

        results = db.query(mainquery)
        return results

# Endpoint
#

class Endpoint:
    def __init__(self,id):
        self.id = int(id)
        self.name = None
        self.desc = None
        self.addrs = []

    def update(self):
        try:
            results = db.query("SELECT * FROM ENDPOINTS"
                     " WHERE id = " + sqlquote(self.id))

            row = results[0]
            self.name = row.name
            self.desc = row.desc
        except:
            pass

    def get_addresses(self):
        return self.addrs

    def get_id(self):
        return self.id

    def get_name(self):
        if self.name == None:
            self.update()
        return self.name

    def get_desc(self):
        if self.desc == None:
            self.update()
        return self.desc

#
# Functions
#

def setup(_db):
    logger.debug("setup")
    global db
    db = _db

def setup_sqlite(dbname):
    logger.debug("setup_sqlite(%s)",dbname)
    global db
    db = database(dbn="sqlite",db=dbname)

def list_devices():
    logger.debug("list_devices")
    try:
        listdev = []
        results = db.query("SELECT * FROM [VIEW_DEVICES]")
        for x in results:
            listdev.append(Device(x))
        return listdev
    except:
        return []

def device_from_endpoint_addr(endpoint,addr):
    logger.debug("device_from_endpoint_addr((%s,%s)",endpoint,addr)
    try:
        results = db.query(
            "SELECT DEVICES.* FROM DEVICES, ENDPOINT_ADDRS"
            " WHERE DEVICES.id = ENDPOINT_ADDRS.device AND"
            " ENDPOINT_ADDRS.address = " + sqlquote(addr) +
            " ENDPOINT_ADDRS.endpoint = " + sqlquote(endpoint)
            )
        dev = Device(results[0])
        return dev
    except:
        return None

